
from sqlalchemy import Column, Integer, String, Float, DateTime, UniqueConstraint, ForeignKey

from QualityInspectionViolation.DbInit.mysqlDb import Base
from QualityInspectionViolation.Model.Department import Department


class Adviser(Base):
    __tablename__ = 'adviser'
    id = Column(Integer, primary_key=True)
    name = Column(String(32), index=True)
    departmentId = Column(String(255))
    departmentName = Column(String(32))
    groupId = Column(String(255))
    roleId = Column(Integer)
    roleName = Column(String(32))
    createdAt = Column(DateTime)
    updatedAt = Column(DateTime)

    def queryAdviserByOrganId(self, organId, session):
        AdviserList = session.query(Adviser).join(Department, Adviser.departmentId == Department.id).filter(Department.organId.like(organId+"%")).all()
        return AdviserList

    def queryAdviserByGroupId(self, groupId, session):
        AdviserList = session.query(Adviser).filter(Adviser.groupId == groupId).all()
        return AdviserList

    def queryGroupLeaderAdvise(self, organId, organLen, pageSize, pageNo, session):
        adviserList = session.execute('select * from adviser where groupId in (select a.groupId from adviser a inner join department d on a.departmentId = d.id where d.organId like :organId and length(d.organId) =:organLen group by a.groupId) and roleId = 4 limit :pageLimt offset :pageOffset',
            params={"organId": organId + "%", "organLen": organLen, "pageLimt": pageSize, "pageOffset": (pageNo - 1) * pageSize}).fetchall()

        # select *
        # from adviser
        # where groupId in (select a.groupId from adviser a inner join department d on a.departmentId = d.id
        #                     where d.organId like = :organId and length(d.organId) =:organLen
        #                     group by d.groupId)
        # and `role` = 4 limit :pageSize offset :pageOffset

        return adviserList

    def queryGroupAdvise(self, organId, organLen, pageSize, pageNo, session):
        adviserList = session.execute('select a.* from adviser a inner join department d on a.departmentId = d.id where d.organId like :organId and length(d.organId) =:organLen and a.roleId = 4 limit :pageLimit offset :pageOffset',
                                  params={"organId": organId + "%", "organLen": organLen, "pageLimit": pageSize,"pageOffset": (pageNo - 1) * pageSize}).fetchall()
        return adviserList

    def queryNoGroupAdvise(self, organId, organLen, pageSize, pageNo, session):
        adviserList = session.execute('select a.* from adviser a inner join department d on a.departmentId = d.id where d.organId like :organId and length(d.organId) =:organLen and a.roleId <> 4 limit :pageLimit offset :pageOffset',
                                  params={"organId": organId + "%", "organLen": organLen, "pageLimit": pageSize,"pageOffset": (pageNo - 1) * pageSize}).fetchall()
        return adviserList

    def queryAdvise(self, organId, organLen, pageSize, pageNo, session):
        adviserList = session.execute('select a.* from adviser a inner join department d on a.departmentId = d.id where d.organId like :organId and length(d.organId) =:organLen limit :pageLimit offset :pageOffset',
                                  params={"organId": organId + "%", "organLen": organLen, "pageLimit": pageSize,"pageOffset": (pageNo - 1) * pageSize}).fetchall()
        return adviserList






















